package com.cxx.purchasecharge.dal.impl;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;

import com.cxx.purchasecharge.core.model.UserAuthority;
import com.cxx.purchasecharge.core.model.persistence.User;
import com.cxx.purchasecharge.dal.UserDaoPlus;
import com.cxx.purchasecharge.dal.common.GenericDaoUtils;

public class UserDaoImpl implements UserDaoPlus
{
    private static final Logger logger = Logger.getLogger (UserDaoImpl.class);

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public List <User> findByName (String name)
    {
        logger.debug ("Find user by name: " + name);
        String key = (null == name ? "" : name.trim ());
        // String sql =
        // "select u from User u where u.userId like ?1 or u.firstName like ?2 or u.lastName like ?3 order by u.userId";
        String sql = "select * from pc_user u where u.userId like ?1 or u.firstName like ?2 or u.lastName like ?3 order by convert(u.userId USING gbk) COLLATE gbk_chinese_ci asc";
        Query query = entityManager.createNativeQuery (sql, User.class);
        query.setParameter (1, key + "%");
        query.setParameter (2, key + "%");
        query.setParameter (3, key + "%");
        @SuppressWarnings ("unchecked")
        List <User> users = query.getResultList ();
        return users;
    }

    @Override
    public Page <User> findByFuzzy (Pageable pageable, String searchKey)
    {
        String pageableLog = "offSet:" + pageable.getOffset () + ",pageNumber:" + pageable.getPageNumber ()
                             + ",pageSize:" + pageable.getPageSize () + ",sort:" + pageable.getSort ();
        logger.debug ("Find paging user by fuzzy, pageable: (" + pageableLog + "), searchkey: " + searchKey);
        String keyWord = (null == searchKey ? "" : searchKey.trim ());
        String selectCountSql = "SELECT count(u) ";
        String fromSql = "FROM User u where " + "u.userId like ?1 or " + "u.firstName like ?2 or "
                         + "u.lastName like ?3 or " + "u.mobilePhone like ?4 or " + "u.email like ?5";
        Query countQuery = entityManager.createQuery (selectCountSql + fromSql);
        countQuery.setParameter (1, keyWord + "%");
        countQuery.setParameter (2, keyWord + "%");
        countQuery.setParameter (3, keyWord + "%");
        countQuery.setParameter (4, keyWord + "%");
        countQuery.setParameter (5, keyWord + "%");
        int total = Integer.parseInt (countQuery.getResultList ().get (0).toString ());

        String querySql = "SELECT u ";
        querySql += fromSql;
        String orderBySql = " ORDER BY ";
        querySql += orderBySql;
        String sortSqlStr = GenericDaoUtils.parseSort (User.class, pageable, "u");
        if (!"".endsWith (sortSqlStr))
        {
            querySql += sortSqlStr;
        }
        else
        {
            querySql += "u.userId asc";
        }

        Query query = entityManager.createQuery (querySql);
        query.setParameter (1, keyWord + "%");
        query.setParameter (2, keyWord + "%");
        query.setParameter (3, keyWord + "%");
        query.setParameter (4, keyWord + "%");
        query.setParameter (5, keyWord + "%");

        query.setFirstResult (pageable.getOffset ());
        query.setMaxResults (pageable.getPageSize ());

        @SuppressWarnings ("unchecked")
        List <User> userLists = query.getResultList ();
        Page <User> userPage = new PageImpl <User> (userLists, pageable, total);
        return userPage;
    }

    @Override
    public List <UserAuthority> findUserAuthorityByUserId (String userId)
    {
        logger.debug ("Find UserAuthority by userId: " + userId);
        if (StringUtils.isNotBlank (userId))
        {
            String sql = "select u.userId as user, a.name as authority from pc_authority a, pc_role r, pc_role_authority ra, pc_user u, pc_user_role ur where u.id=ur.user_id and r.id=ur.role_id and a.id=ra.authority_id and r.id=ra.role_id and u.userId=?1";
            Query query = entityManager.createNativeQuery (sql, UserAuthority.class);
            query.setParameter (1, userId);
            @SuppressWarnings ("unchecked")
            List <UserAuthority> userAuthorities = query.getResultList ();
            return userAuthorities;
        }
        return null;
    }
    
}
